select 'print "create indexes references to current db"'+char(10)+char(10)

set nocount on
go

DECLARE indexes CURSOR FOR
 SELECT 		
        si.status,
		si.status2,
		si.indid,
		so.name,
		user_name(so.uid)+'.'+so.name,
		si.name
  FROM 		
        sysindexes si, 
		sysobjects so
 WHERE 		
        si.id           = so.id  AND
     	si.indid        > 0      AND
     	si.indid        < 255    AND
     	si.status2 & 2  != 2     AND
        so.type         = 'U'    AND
        so.type!        = 'S'            
ORDER BY 	
        so.uid, so.name, si.indid
go

CREATE TABLE #tmp(
	id      numeric(10) identity,
	txt     varchar(255),
	primary key (id)
)
go

declare @dbo_tabname    varchar(255) 
declare @tabname        varchar(255) 
declare @indname        varchar(255) 
declare @txt            varchar(255)
declare @i              int 
declare @indid          int
declare @status         int 
declare @status2        int

OPEN    indexes
FETCH   indexes INTO @status,@status2,@indid,@tabname, @dbo_tabname,@indname
WHILE @@sqlstatus = 0  
BEGIN
	SELECT  @txt = '' 
    SELECT  @i = 1 
    
    INSERT INTO #tmp (txt)
    SELECT "IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('" + @dbo_tabname + "') AND name='"+ @indname + "') BEGIN    DROP INDEX " + @tabname + "."+ @indname +" END" + char(13) + char(10)
    INSERT INTO #tmp (txt) SELECT 'go' + char(13) + char(10)

            
	WHILE  @i   <= 31
	BEGIN
		SELECT @txt = @txt + index_col(@dbo_tabname, @indid, @i)+ ' ' + index_colorder(@dbo_tabname, @indid, @i)
		if (index_col(@dbo_tabname, @indid, @i+1) is null) break
		if @i >= 1 SELECT @txt = @txt + ","
		SELECT @i = @i + 1
	END
	
	SELECT @txt=' (' +isnull(@txt,'') +')'

	INSERT INTO #tmp(txt)
	SELECT 
		'create' +	case @status & 2 when 2 then ' unique ' else ' ' end +
		case (@status & 16) + (@status2 & 512) when 0 then 'nonclustered' else 'clustered' end +
		' index ' +	@indname + ' on ' + @dbo_tabname +@txt + char(13) + char(10)

	SELECT @txt=''
	if @status & 1 = 1      SELECT @txt = 'ignore_dup_key'
	if @status & 4 = 4      SELECT @txt = 'ignore_dup_row'
	if @status & 64 = 64    SELECT @txt = 'allow_dup_row'
	
	if @txt<>''
		INSERT INTO #tmp (txt)
			SELECT char(9)+"with " + @txt + char(13) + char(10)

	INSERT INTO #tmp (txt)	SELECT 'go' + char(13) + char(10)

	FETCH indexes INTO @status,@status2,@indid,@tabname,@dbo_tabname,@indname
end
go
close indexes
deallocate cursor indexes
go
select txt from #tmp order by id
go
drop table #tmp
go
set nocount off
go

